package com.ruozedata.bigdata.homework.homework02

import scalikejdbc._
import scalikejdbc.config.DBs

case class Emp(empno:Int, ename :String, job : String, mgr:Int, hirdate:String, sal:Double, deptno:Int)

object ScalaLikeJdbc {

    def main(args: Array[String]): Unit = {

        // 加载驱动
        //解析application.conf的文件
        DBs.setup()
        // 创建表
        createTable()
        // 插入数据
        insert()
        // 更新数据
        update()
        // 删除数据
        delete()
        // 查询数据
        query()
    }

    def createTable(): Unit = {
        DB.autoCommit { implicit session =>
            SQL("create table emp( " +
                "id int not null auto_increment," +
                " empno int not null," +
                " ename varchar(100), " +
                " job varchar(100), " +
                " mgr int, " +
                " deptno int, " +
                " hirdate varchar(100), " +
                " sal float(5,2), " +
                " primary key ( id ) " +
                ")engine=innodb default charset=utf8; "
            ).execute.apply()
        }
    }

    def insert() = {
        // 插入使用的是localTx
        DB.localTx(implicit session => {
          SQL(
              """INSERT INTO emp(
                    empno,
                    ename,
                    job,
                    mgr,
                    hirdate,
                    sal,
                    deptno)
                VALUES(
                    1,
                    'Andy',
                    'Engineer',
                    20,
                    '1990-10-20',
                    880.00,
                    100);
                """.stripMargin).update.apply()
        })
    }

    def update() = {
        // 更新使用的是autoCommit
        DB.autoCommit(implicit session => {
          // SQL里面是普通的sql语句 ，update().apply()是执行语句
          SQL("UPDATE emp SET sal = 100 WHERE ename = 'Andy';").update().apply()
        })
    }

    def delete() = {
        // 删除使用的也是autoCommit
        DB.autoCommit(implicit session => {
          SQL("DELETE FROM emp WHERE id = 2 ;").update().apply()
        })
    }

    def query() ={
        // 读取使用的是readOnly
        val empers = DB.readOnly(implicit session => {
            SQL("select * from emp").map(rs => {
                Emp(
                    rs.int("empno"),
                    rs.string("ename"),
                    rs.string("job"),
                    rs.int("mgr"),
                    rs.string("hirdate"),
                    rs.double("sal"),
                    rs.int("deptno")
                )
            }).list().apply()
        })
        empers.foreach(println)
    }

}
